-- @owner: guoweibiao
-- @date: 2022/1/13
-- @testpoint: 创建建ustore表函数

--step1：创建建表函数;expect:成功
create or replace function create_tables(
table_num_in integer,
tb_name varchar)
returns void
cost 100
volatile
as $$
declare
v_idx integer := 0;
v_strtable varchar :='';
v_strsql varchar :='';
begin
while v_idx < table_num_in loop
v_idx = v_idx+1;
v_strtable = concat(tb_name, v_idx);
v_strsql = 'create table '||v_strtable||'(c_id int,c_d_id int,c_w_id int,c_first varchar(16),c_middle char(2),c_last varchar(16),c_street_1 varchar(20),c_street_2 varchar(20),c_city varchar(20),c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,c_credit char(2),c_credit_lim numeric(12,2),c_discount numeric(4,4),c_balance numeric(12,2),c_ytd_payment real,c_payment_cnt number,c_delivery_cnt bool,c_end date,c_vchar varchar(1000),c_data clob,c_text bytea) with (storage_type=ustore);';
execute v_strsql;
end loop;
end
$$ language plpgsql;
/


create or replace function create_indexs(
table_num_in integer,
tb_name varchar,
tb_index varchar)
returns void
cost 100
volatile
as $$
declare
v_idx integer := 0;
v_strtable varchar :='';
v_strsql varchar :='';
v_strindex varchar :='';
begin
while v_idx < table_num_in loop
v_idx = v_idx+1;
v_strindex = concat(tb_index, v_idx);
v_strtable = concat(tb_name, v_idx);
v_strsql = 'create index '||v_strindex||' on '||v_strtable||'(c_id)';
execute v_strsql;
end loop;
end
$$ language plpgsql;
/

create or replace function drop_tables(
    table_num_in integer,
    tb_name varchar)
    returns void
    cost 100
    volatile
as $$
declare
v_idx integer :=0;
v_strtable varchar :='';
v_strsql varchar :='';
begin
while v_idx < table_num_in loop
  v_idx = v_idx+1;
  v_strtable = concat(tb_name, v_idx);
  v_strsql = 'drop table if exists '||v_strtable;
  execute v_strsql;
  end loop;
 end
$$ language plpgsql;
/

create or replace function add_tables_data(
    table_num_in integer,
    st integer,
    ed integer,
    tb_name varchar)
    returns void
    cost 100
    volatile
as $$
declare
v_idx integer := 0;
v_strtable varchar :='';
v_strsql varchar :='';
begin
    while v_idx < table_num_in loop
    v_idx = v_idx+1;
    v_strtable = concat(tb_name, v_idx);
    v_strsql = 'insert into '||v_strtable||' values(generate_series('||st||','||ed||'),generate_series('||st||','||ed||'),generate_series('||st||','||ed||'),''is1cmrds1'',''oe'',''bar1bar1'',''bkili1fcrrgf1'',''pmbwo1vhsdgj1'',''dyf1rda1'',''uq'',48011,94012151,to_date(''2020-5-6 10:10:10'',''yyyy-mm-dd hh:mi:ss''),''gc'',50000.0,0.4361328,-10.0,10.0,1,true,to_date(''2020-5-10 10:10:10'',''yyyy-mm-dd hh:mi:ss''),''cllgfsmntecc348493214893542npfzcsfjlufvcdsf1'',''gfvmgfvdfcmkgvnpfzcsykxxyscdsfqvld1'',''1234354587643123455213445656'');';
    execute v_strsql;
  end loop;
end
$$ language plpgsql;
/

create or replace function create_partition_tables(
table_num_in integer,
tb_name varchar)
returns void
cost 100
volatile
as $$
declare
v_idx integer := 0;
v_strtable varchar :='';
v_strsql varchar :='';
begin
while v_idx < table_num_in loop
v_idx = v_idx+1;
v_strtable = concat(tb_name, v_idx);
v_strsql = 'create table '||v_strtable||'(c_id int,c_d_id int,c_w_id int,c_first varchar(16),c_middle char(2),c_last varchar(16),c_street_1 varchar(20),c_street_2 varchar(20),c_city varchar(20),c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,c_credit char(2),c_credit_lim numeric(12,2),c_discount numeric(4,4),c_balance numeric(12,2),c_ytd_payment real,c_payment_cnt number,c_delivery_cnt bool,c_end date,c_vchar varchar(1000),c_data clob,c_text bytea) with (storage_type=ustore) partition by range(c_id) (partition p1 values less than (50000),partition p2 values less than (100000),partition p3 values less than (150000));';
execute v_strsql;
end loop;
end
$$ language plpgsql;
/

--step2：清理环境;expect:成功
drop function create_tables;
drop function create_indexs;
drop function drop_tables;
drop function add_tables_data;
drop function create_partition_tables;